Ralph Barnes¶
On my honor, as a student, I have neither given nor received unauthorized aid on this academic work. The assignment I am submitting is my own containing my own codes and words.
In [1]:
# import packages
import requests as rq
import pandas as pd
import numpy as np
import random as rd
from lxml import html
import csv
# for plotting
import matplotlib as plt
%matplotlib inline
import plotly
import plotly.graph_objs as go
from plotly.offline import download_plotlyjs, init_notebook_mode, iplot
from plotly.graph_objs import *
init_notebook_mode()
import seaborn as sns
Data Cleaning Tutorial¶
Data Analysis cannot be successful if the data to be analyzed is incomplete. Performing statistical calculations on age, for example, could be disasterous if someone is mislabeld as being 350 years old!¶
Data cleaning is 90% of data analytics. Bad data = bad analytics!¶
Assistance with this tutorial came from DataCamp (multiple examples).¶
https://campus.datacamp.com/courses/cleaning-data-in-python/exploring-your-data¶
Other assistance from¶
Pandas Pydata https://pandas.pydata.org/pandas-docs/stable/missing_data.html¶
GitHub https://github.com/KarrieK/pandas_data_cleaning¶
Reading in the dataset.¶
At this point, you should know some basic Python commands. For example, let's read in our test data file for this tutorial.¶
In [2]:
# Import csv file into dataframe (df) for data cleaning
# data from NYC Open Data (https://data.cityofnewyork.us/Housing-Development/DOB-Job-Application-Filings/ic3t-wcy2)
df = pd.read_csv('data/dob_job_application_filings.csv', sep=',', low_memory=False)
In [3]:
# copy dataset
df2 = df.copy()
In [4]:
# To see and display top 5 rows
df2.head()
Out[4]:
This file has some basic information: Job #, House #, etc.¶
In [5]:
# See the end (tail) of the data, or last 5 rows
df2.tail()
Out[5]:
We can use .shape and .columns to see the data. Since these are not methods, there is no need to use parenthesis.¶
In [6]:
# See the shape of the data.
df2.shape
Out[6]:
So there are 322,530 rows and 82 columns in this dataset.¶
In [7]:
# See the columns
df2.columns
Out[7]:
To find out the data types, we need to use the info() function.¶
In [8]:
# Find out data types
df2.info()
Handing missing or bad data.¶
In [9]:
# Find frequencies of specific landmarked column data using dot notation. Because of the amont of data, we can shorten
# our search to the first 5 rows by adding .head() at the end. This is a way to slice multiple methods into one statement.
# df['Zip'].value_counts(dropna=False) would be the bracket notation and provide the same results.
df2.Landmarked.value_counts(dropna=False).head()
Out[9]:
In [10]:
# we can also search the last 5 rows by using tail()
df2.Landmarked.value_counts(dropna=False).tail()
Out[10]:
In [11]:
# If a column name has a space, it is best to use the bracket notation.
df2['Fully Paid'].value_counts(dropna=False).head()
Out[11]:
What's this NaN? Ah, we seem to have found some null values.¶
In [12]:
# General summary of data
df2.describe()
Out[12]:
As you see, this provides the count, mean, standard deviation and other statistical summaries from the data.¶
Uh oh, there's more of those NaN values in the numerical columns now. So, let's look at the PC Filed column.¶
In [13]:
# View data in PC Filed column to find missing values
df2['PC Filed'].describe()
Out[13]:
Since this is obviously a flawed column, we can simply drop this column using drop()¶
In [14]:
# using df3 variable so as to continue work with df2 dataset
df3 = df2.copy()
df3 = df3.drop(['PC Filed'], axis=1)
df3.head()
Out[14]:
In [15]:
# see info of last 5 rows.
df3.tail()
Out[15]:
As we can see, there is still NaN or null values within the data.¶
We can do several things with the missing data. We have already used dropna(), but there are more ways to fill in the data for a particular cell.¶
Let's use df# so as to retain the original df2 dataset, but show the variations of cleaning data.¶
Other methods of handling bad data.¶
In [16]:
# using ffill() function to forward fill data with zero
df4 = df2.copy()
df4 = df4.ffill(0)
df4.head()
Out[16]:
In [17]:
# using bfill() function to back fill data with the value of 1
df5 = df2.copy()
df5 = df5.bfill(1)
df5.head()
Out[17]:
In [18]:
# display the last 5 rows
df5.tail()
Out[18]:
There are other ways to handle null value.¶
In [19]:
# copy before testing
df6 = df2.copy()
# replace null value with the median value
df6 = df6.fillna({'Age': 28})
df6.isnull().sum()
Out[19]:
In [20]:
# replace null value with the most popular value in a categorial columns
df6 = df6.fillna({'Embarked': 'S'})
df6.isnull().sum()
Out[20]:
In [37]:
# hello in Owner's Business name is inaccurate ...replacing inaccurate value with null value
df6 = df6.replace('None', np.nan)
df6.head()
Out[37]:
In [36]:
# 350 is an inaccurate value for age and should be replaced by 35
# changinge single value with replace
df6 = df6.replace({"Owner's Business Name": 'NA'}, {"Owner's Business Name": 'No Name Given'})
df6.head()
Out[36]:
What if the data that is bad (or missing) is a time or date stamp?¶
You can replace the bad data, if you know the column, by pandas object.¶
In [23]:
# pandas object
df7 = df2.copy()
# for one column
df7['Pre- Filing Date'] = pd.Timestamp('20130425')
df7.tail()
Out[23]:
In [24]:
# for multiple columns
df7['Paid'] = pd.Timestamp('20130425')
df7['Fully Paid'] = pd.Timestamp('20130425')
df7['Assigned'] = pd.Timestamp('20130425')
df7['Approved'] = pd.Timestamp('20130425')
df7['Fully Permitted'] = pd.Timestamp('20130425')
This would be handy if you wanted to replace the data with known data, just so there's something listed.¶
In [25]:
# drop all columns that contain missing or null data.
df8 = df2.copy()
df8 = df8.dropna(axis=1)
df8.head()
Out[25]:
In [26]:
# last 5 rows
df8.tail()
Out[26]:
So what data is left after dropping all rows with missing data?¶
In [27]:
# data info
df8.info()
We can also use visualization techniques to find the bad data.¶
First, set up the X and Y variables.¶
In [28]:
df9 = pd.DataFrame(df2)
df9['x'] = df9['Existing Zoning Sqft']
df9['y'] = df9['Proposed Zoning Sqft']
In [29]:
# Scatter plot
sns.lmplot('x', 'y', data=df9, fit_reg=False)
Out[29]:
In [30]:
# Box plot
sns.boxplot([df9.x, df9.y])
Out[30]:
Any bad value will be outside of the box.¶
In [31]:
# Density plot
sns.kdeplot(df9['x'])
Out[31]:
Here it would be the value that is "out of scope" compared to all other values.¶
Using plotly to visualize csv.¶
In [32]:
# iplot "scatterplot" for visualization.
dt = [Scatter(x=df9.x, y=df9.y, mode = 'markers')]
iplot(dt)
Pivot tables can also be used to see your data.¶
In [33]:
# create a pivot, which helps you develop insights about the data in question.
df10 = df8.copy()
df10.groupby('Job #').count()
Out[33]:
Once you have cleaned your data, you can save it back again.¶
In [34]:
# save back to csv file (this example is only partially clean; you want to make sure you have used all code to clean the data first)
df10.to_csv("data/cleaned_data.csv", encoding="utf-8")